USE Fresher14_G1
GO

---------------------------TABLE Role---------------------------
---CREATE PROC sp_CreateRole
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_CreateRole' AND TYPE = 'P')
DROP PROC sp_CreateRole
GO

	CREATE PROC sp_CreateRole
	@RoleName nvarchar(30),
	@Status bit
	AS
	BEGIN
		INSERT INTO [Role] VALUES(NEWID(), @RoleName, @Status)
		INSERT INTO SystemEventLog(SystemEventType, Description) VALUES('Infomation', 'Create new Role')
	END
	GO

---CREATE PROC sp_CheckRoleNameExisted
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_CheckRoleNameExisted' AND TYPE = 'P')
DROP PROC sp_CheckRoleNameExisted
GO
	CREATE PROC sp_CheckRoleNameExisted
	@RoleName nvarchar(30)
	AS
	BEGIN
	SELECT COUNT(*) FROM [Role] WHERE RoleName=@RoleName
	END
	GO

---CREATE PROC sp_GetAllRole
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_GetAllRole' AND TYPE = 'P')
DROP PROC sp_GetAllRole
GO
	CREATE PROC sp_GetAllRole
	AS
	BEGIN
		SELECT * FROM [Role]
	END
	GO

---CREATE PROC sp_ChangeStatusRole
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_ChangeStatusRole' AND TYPE = 'P')
DROP PROC sp_ChangeStatusRole
GO
	CREATE PROC sp_ChangeStatusRole
	@RoleId UNIQUEIDENTIFIER,
	@Status BIT
	AS
	BEGIN
		UPDATE [Role] SET Status=@Status WHERE RoleId=@RoleId
	END
	GO

---CREATE PROC sp_GetRoleById
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_GetRoleById' AND TYPE = 'P')
DROP PROC sp_GetRoleById
GO
	CREATE PROC sp_GetRoleById
	@RoleId UNIQUEIDENTIFIER
	AS
	BEGIN
		SELECT * FROM [Role] WHERE RoleId = @RoleId
	END
	GO

---CREATE PROC sp_UpdateRoleById
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='sp_UpdateRoleById' AND TYPE = 'P')
DROP PROC sp_UpdateRoleById
GO
	CREATE PROC sp_UpdateRoleById
	@RoleId UNIQUEIDENTIFIER,
	@RoleName nvarchar(30),
	@Status bit
	AS
	BEGIN
		UPDATE [Role] SET RoleName = @RoleName, Status = @Status WHERE RoleId = @RoleId
	END
	GO



